
//
//  DBHelper.m
//  leo
//
//  Created by 常艳 on 15/8/24.
//  Copyright (c) 2015年 mdx. All rights reserved.
//

#import "DBHelper.h"

NSString * const tag=@"DBHelper------------------------";

@implementation DBHelper

- (id)init
{
    db=[FMDatabase databaseWithPath:[self dataFilePath]];
    queue=[FMDatabaseQueue databaseQueueWithPath:[self dataFilePath]];
    return self;
}
-(void)dealloc
{
    db=NULL;
    queue=NULL;
}
//获取document目录并返回数据库目录
- (NSString *)dataFilePath{
    
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    //NSLog(@"=======%@",documentsDirectory);
    return [documentsDirectory stringByAppendingPathComponent:@"leo.sqlite"];//这里很神奇，可以定义成任何类型的文件，也可以不定义成.db文件，任何格式都行，定义成.sb文件都行，达到了很好的数据隐秘性
    
}
-(NSString *)versionFilePath
{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSLog(@"=======%@",documentsDirectory);
    return [documentsDirectory stringByAppendingPathComponent:@"info.plist"];
}
-(int) dbVersionNumber
{
    NSLog(@"%@,dbVersionNumber entered",tag);
    NSString *filePath=[self versionFilePath];
    //文件管理器
    NSFileManager *fileManager = [NSFileManager defaultManager];
    //判断数据库是否存在
    BOOL find = [fileManager fileExistsAtPath:filePath];
    if(find==NO)
    {
        NSLog(@"%@,dbVersionNumber,not find the version file,return 0",tag);
        return 0;
    }
    else
    {
        NSDictionary *dict=[[NSDictionary alloc]  initWithContentsOfFile:filePath];
        if(dict==NULL)
        {
            NSLog(@"%@,dbVersionNumber,dict is null,return 0",tag);
            return 0;
        }
        NSNumber *versionNumber=[dict objectForKey:@"CURRENT_VERSION"];
        if(versionNumber==NULL)
        {
            NSLog(@"%@,dbVersionNumber,dict is null,return 0",tag);
            return 0;
        }
        NSLog(@"%@,dbVersionNumber,version number is %@",tag,versionNumber);
        return  [versionNumber intValue];
    }
}
//数据库升级完成后更改版本号
-(BOOL)updateDBVersion:(NSNumber *)upgradeVersion
{
    NSLog(@"%@,updateDBVersion entered.",tag);
    NSString *configPath=[self versionFilePath];
    //文件管理器
    NSFileManager *fileManager = [NSFileManager defaultManager];
    //判断数据库是否存在
    BOOL find = [fileManager fileExistsAtPath:configPath];
    NSMutableDictionary *dict=NULL;
    if(find==NO)//文件不存在，创建文件，并将版本号写入
    {
         NSLog(@"%@,updateDBVersion,not find version file",tag);
        [fileManager createFileAtPath:configPath contents:nil attributes:nil];
        dict=[[NSMutableDictionary alloc] init];
        [dict setObject:upgradeVersion forKey:@"CURRENT_VERSION"];
        [dict writeToFile:configPath atomically:YES];
    }
    else//读取版本号，并修改版本号
    {
        NSLog(@"%@,updateDBVersion, find version file",tag);
        dict=[[[NSMutableDictionary alloc] initWithContentsOfFile:configPath] mutableCopy];
        NSNumber *versionValue=[dict objectForKey:@"CURRENT_VERSION"];
        versionValue=upgradeVersion;
        [dict setObject:versionValue forKey:@"CURRENT_VERSION"];
        [dict writeToFile:configPath atomically:YES];
    }
    NSLog(@"%@,updateDBVersion,update version sucess,version number is %@",tag,upgradeVersion);
    return YES;
    
}
//创建，打开数据库
- (BOOL)createDB {
    
    //获取数据库路径
    NSString *path = [self dataFilePath];
    NSLog(@"%@,db path is %@.",tag,path);
    //文件管理器
    NSFileManager *fileManager = [NSFileManager defaultManager];
    //判断数据库是否存在
    BOOL find = [fileManager fileExistsAtPath:path];
    
    //如果数据库存在，则用sqlite3_open直接打开（不要担心，如果数据库不存在sqlite3_open会自动创建）
    if (find) {
        
        NSLog(@"Database file have already existed.");
        
        NSString *configPath=[[NSBundle bundleForClass:[self class]] pathForResource:@"DBConfig" ofType:@"plist"];
        NSDictionary *configTalbe=[[NSDictionary alloc] initWithContentsOfFile:configPath];
        if(configTalbe==NULL)
        {
            NSLog(@"%@,createDB,configTable is null",tag);
            return NO;
        }
        if(configTalbe!=NULL)
        {
            NSNumber *upgradeVersion=[configTalbe objectForKey:@"UPGRADE_VERSION"];
            int currentVersion=[self dbVersionNumber];
            if([upgradeVersion intValue]!=currentVersion)
            {
                if(db!=NULL&&[db open]){
                    //创建一个新表
                    [self deleteTables];
                    [self createTables];
                    [db close];
                    [self updateDBVersion:upgradeVersion];
                    return YES;
                } else {
                    //如果创建并打开数据库失败则关闭数据库
                    if(db!=NULL)
                        [db close];
                    NSLog(@"Error: open database file.");
                    return NO;
                }
            }
        }
        
        return YES;
    }
    //如果发现数据库不存在则利用sqlite3_open创建数据库（上面已经提到过），与上面相同，路径要转换为C字符串
    if(db!=NULL&&[db open]){
        //创建一个新表
        [self createTables];
        if(db!=NULL)
            [db close];
        NSString *configPath=[[NSBundle bundleForClass:[self class]] pathForResource:@"DBConfig" ofType:@"plist"];
        NSDictionary *configTalbe=[[NSDictionary alloc] initWithContentsOfFile:configPath];
        NSNumber *upgradeVersion=[configTalbe objectForKey:@"UPGRADE_VERSION"];
        [self updateDBVersion:upgradeVersion];
        return YES;
    } else {
        //如果创建并打开数据库失败则关闭数据库
        if(db!=NULL)
            [db close];
        NSLog(@"Error: open database file.");
        return NO;
    }
    return NO;
}
-(BOOL)openDB
{
    //获取数据库路径
    NSString *path = [self dataFilePath];
    //文件管理器
    NSFileManager *fileManager = [NSFileManager defaultManager];
    //判断数据库是否存在
    BOOL find = [fileManager fileExistsAtPath:path];
    
    //如果数据库存在，则用sqlite3_open直接打开（不要担心，如果数据库不存在sqlite3_open会自动创建）
    if (!find) {
        
        NSLog(@"%@,Database file is not existed.",tag);
        return NO;
    }
    //如果发现数据库不存在则利用sqlite3_open创建数据库（上面已经提到过），与上面相同，路径要转换为C字符串
    if(db!=NULL&&[db open]){
        return YES;
    } else {
        //如果创建并打开数据库失败则关闭数据库
        if(db!=NULL)
            [db close];
        NSLog(@"%@,Error: open database file.",tag);
        return NO;
    }
 
}
//主要是用于前期测试使用
-(BOOL) deleteTables
{

        NSLog(@"%@,deleteTables eneter",tag);
        char * delete_user_sql="drop table if exists account;";
        char * delete_watch_sql="drop table if exists watch;";
        char * delete_manager_sql="drop table if exists manager;";
        //char * delete_bind_sql="drop table if exists bind;";
        char * delete_upgrade_sql="drop table if exists upgrade;";
        char * delete_grow_sql="drop table if exists grow;";
        char * delete_systask_sql="drop table if exists sys_task;";
        char * delete_task_sql="drop table if exists task;";
        char * delete_theme_sql="drop table if exists theme";
        char * delete_themeLock_sql="drop table if exists theme_lock";
        char * delete_themeLevel_sql="drop table if exists theme_level";
        char * delete_position_sql="drop table if exists position;";
        char * delete_fence_sql="drop table if exists fence;";
        char * delete_event_sql="drop table if exists event;";
        char * delete_remind_sql="drop table if exists remind;";
        char * delete_chart_sql="drop table if exists chart;";
        char * delete_message_sql="drop table if exists message;";
        
        if(![db executeStatements:delete_user_sql])
        {
            NSLog(@"%@,Error: sql is %s",tag,delete_user_sql);
            return NO;
        }
        if(![db executeStatements:delete_watch_sql])
        {
            NSLog(@"%@,Error: sql is %s",tag,delete_watch_sql);
            return NO;
        }
        if(![db executeStatements:delete_manager_sql])
        {
            NSLog(@"%@,Error: sql is %s",tag,delete_manager_sql);
            return NO;
        }
        /*
         if(sqlite3_exec(database, delete_bind_sql, NULL, NULL, &errorMsg)!=SQLITE_OK)
         {
         NSLog(@"%@,Error: sql is %s,%s",tag,delete_bind_sql,errorMsg);
         return NO;
         }*/
        if(![db executeStatements:delete_upgrade_sql])
        {
            NSLog(@"%@,Error: sql is %s",tag,delete_upgrade_sql);
            return NO;
        }
        if(![db executeStatements:delete_grow_sql])
        {
            NSLog(@"%@,Error: sql is %s",tag,delete_grow_sql);
            return NO;
        }
        if(![db executeStatements:delete_systask_sql])
        {
            NSLog(@"%@,Error: sql is %s",tag,delete_systask_sql);
            return NO;
        }
        if(![db executeStatements:delete_task_sql])
        {
            NSLog(@"%@,Error: sql is %s",tag,delete_task_sql);
            return NO;
        }
        if(![db executeStatements:delete_theme_sql])
        {
            NSLog(@"%@,Error: sql is %s",tag,delete_theme_sql);
            return NO;
        }
        if(![db executeStatements:delete_themeLock_sql])
        {
            NSLog(@"%@,Error: sql is %s",tag,delete_themeLock_sql);
            return NO;
        }
        if(![db executeStatements:delete_themeLevel_sql])
        {
            NSLog(@"%@,Error: sql is %s",tag,delete_themeLevel_sql);
            return NO;
        }
        if(![db executeStatements:delete_position_sql])
        {
            NSLog(@"%@,Error: sql is %s",tag,delete_position_sql);
            return NO;
        }
        if(![db executeStatements:delete_fence_sql])
        {
            NSLog(@"%@,Error: sql is %s",tag,delete_fence_sql);
            return NO;
        }
        if(![db executeStatements:delete_event_sql])
        {
            NSLog(@"%@,Error: sql is %s",tag,delete_event_sql);
            return NO;
        }
        if(![db executeStatements:delete_remind_sql])
        {
            NSLog(@"%@,Error: sql is %s",tag,delete_remind_sql);
            return NO;
        }
        if(![db executeStatements:delete_chart_sql])
        {
            NSLog(@"%@,Error: sql is %s",tag,delete_chart_sql);
            return NO;
        }
        if(![db executeStatements:delete_message_sql])
        {
            NSLog(@"%@,Error: sql is %s",tag,delete_message_sql);
            return NO;
        }
        NSLog(@"%@,deleteTables,delete all tables sucess",tag);
        return YES;

}
//创建表
- (BOOL) createTables{
    
    //这句是大家熟悉的SQL语句
    char *create_user_sql = "create table if not exists account(user_id integer  not null primary key, account varchar(100) not null,password varchar(50) not null,type boolean not null default 1,is_active smallint(1) not null,access_token varchar(200),refresh_token varchar(200),create_time timestamp,nick_name varchar(50),pic_url varchar(200),photohash varchar(200),birth varchar(20),age integer,email varchar(100),address varchar(200));";
    //UI更改
    char *create_watch_sql="create table if not exists watch(id integer  primary key autoincrement,deviceuid integer not null ,qrcode_url varchar(200),sim varchar(20) ,mac varchar(20),version varchar(64),is_latest smallint(1) default 0,nickname varchar(200),gender smallint(1),height float,weight float,birth varchar(20),class varchar(100),pic_url varchar(200),update_time timestamp,age integer,imei varchar(100),photohash varchar(200),report_interval integer,sample_interval integer,is_active smallint(1),account_id integer,group_id integer,battery integer,message_push smallint(1) default 1,task_push samllint(1) default 1,shortNum samllint(1) default 1,message_time varchar(100),task_time varchar(100),role smallint(1) default 1,theme_id integer default 1,watch_theme integer default 0);";
    char *create_manager_sql="create table if not exists manager (id integer  primary key autoincrement,user_id integer  ,alias varchar(50),nick_name varchar(50),pic_url varchar(200),birth varchar(20),phone varchar(20),name varchar(100),update_time timestamp,photohash varchar(200),deviceuid integer not null,type smallint(1) not null,account_id integer not null,group_id integer,is_sos smallint(1) default 0)";
    //char *create_bind_sql="create table if not exists bind(deviceUid integer not null,manager_id integer not null,type smallint(1) not null, user_id integer not null,group_id integer not null)";
    char *create_upgrade_sql="create table if not exists upgrade(id integer  primary key autoincrement,level integer,exp integer)";
    
    //UI更改
    char *create_grow_sql="create table if not exists grow (id integer  primary key autoincrement,deviceuid integer not null,semp integer,empirical integer,step integer,blue_num integer,chat_num integer,call_num integer,task_score integer, create_time timestamp not null,continousdays integer)";
        //UI更改
    char * create_systask_sql="create table if not exists sys_task (id integer  primary key autoincrement,task_id integer not null, type smallint(1),expire int,title varchar(100),description varchar(100),empirical int,status smallint(1),audio_url varchar(200),audio_time int,pic_url varchar(200),is_active smallint(1) default 1,update_time timestamp,create_time timestamp, tips varchar(1000))";
    char * create_task_sql="create table if not exists  task (id integer  primary key autoincrement,owner_uid integer,target_uid integer,task_id integer , type smallint(1),action smallint(1),status smallint(1) default 1,title varchar(200),description varchar(200),exp integer,audio_hash varchar(200),audio_url varchar(200),audio_time int,pic_url varchar(200),random_code varchar(20),expire_time timestamp,create_time timestamp,push_time timestamp,finish_time timestamp,update_time timestamp)";
    char * create_theme_sql="create table if not exists theme (id integer not null primary key autoincrement,theme_id integer,name varchar(50),description varchar(50),theme_url varchar(200),unable_url varchar(200),lab_url varchar(200))";
    char * create_themeLock_sql="create table if not exists theme_lock (id integer not null primary key autoincrement,theme_id integer,level integer,unlock_url varchar(200),lock_url varchar(200))";
    char * create_themeLevel_sql="create table if not exists theme_level (id integer not null primary key autoincrement,theme_id integer,level integer,theme_url varchar(200))";
    char * create_position_sql="create table if not exists position (id integer   primary key autoincrement,deviceuid integer not null,radius float,altitude float,latitude float,longtitude float,accuracy float,type smallint(1),gen_time timestamp,update_time timestamp,locate_type smallint(1),loc varchar(100),main_address varchar(200),sub_address varchar(200),year integer,month integer,day integer,account_id integer)";
    char * create_fence_sql="create table if not exists fence (id integer   primary key autoincrement ,fence_id integer default 0,deviceuid integer not null,latitude float,longtitude float,altitude float,description varchar(100),radius float,title varchar(100),start_time varchar(100),end_time varchar(100),cycle varchar(50),is_active integer not null default 0,type smallint(1),update_time timestamp)";
    char * create_message_sql="create table if not exists message (id integer   primary key autoincrement,msg_type smallint(1), random_id integer,deviceuid  integer ,title varchar(100),uid integer,nick_name varchar(100),value smallint(1) default 0,alias varchar(100),create_time timestamp,update_time timestamp,fence_id integer,loc_id integer,fen_type smallint(1),account_id integer,battery integer,special_time varchar(100),special_name varchar(100),random_code varchar(100),is_delete integer default 0)";
    char * create_event_sql="create table if not exists event (id integer primary key autoincrement,silent_id integer not null,deviceuid integer not null,type smallint(1),start_time timestamp,end_time timestamp,title varchar(50),desc varchar(100),cycle varchar(20),is_active samllint(1),file_url varchar(200),update_time timestamp)";
   char * create_remind_sql="create table if not exists remind (id integer primary key autoincrement,remind_id integer not null,deviceuid integer not null,type smallint(1),remind_time timestamp,title varchar(50),desc varchar(100),cycle varchar(20),is_active samllint(1),audio_url varchar(200),audio_hash varchar(200),update_time timestamp,audio_time integer)";
    char * create_chart_sql="create table if not exists chart (id integer  primary key autoincrement,user_id integer not null,file_url varchar(200),amr_url varchar(200),duration integer,gen_time timestamp,update_time timestamp,group_id integer,deviceuid integer,account_id integer,type integer,targetuid integer,seqid integer,is_read smallint(1),is_view smallint(1),is_send smallint(1) default 0,is_finish smallint(1) default 0);";
    char * init_themeData_sql="insert into theme(theme_id,name,description,theme_url,unable_url,lab_url) values (1,\"玩具总动员\",\"\",\"toy_theme@2x.png\",\"toy_theme_unable@2x.png.png\",\"theme_label_1@2x.png\");insert into theme(theme_id,name,description,theme_url,unable_url,lab_url) values (2,\"小熊维尼\",\"\",\"pooh_theme@2x.png\",\"pooh_theme_unable@2x.png\",\"theme_label_2@2x.png\"); ";
    char * init_themeLock_sql="insert into theme_lock (theme_id,level,unlock_url,lock_url) values (1,0,\"toy_L0_unlock@2x.png\",\"toy_L0_lock@2x.png\");insert into theme_lock (theme_id,level,unlock_url,lock_url) values (1,5,\"toy_L5_unlock@2x.png\",\"toy_L5_lock@2x.png\");insert into theme_lock (theme_id,level,unlock_url,lock_url) values (1,10,\"toy_L10_unlock@2x.png\",\"toy_L10_lock@2x.png\");insert into theme_lock (theme_id,level,unlock_url,lock_url) values (1,15,\"toy_L15_unlock@2x.png\",\"toy_L15_lock@2x.png\");insert into theme_lock (theme_id,level,unlock_url,lock_url) values (2,0,\"pooh_L0_unlock@2x.png\",\"pooh_L0_lock@2x.png\");insert into theme_lock (theme_id,level,unlock_url,lock_url) values (2,5,\"pooh_L5_unlock@2x.png\",\"pooh_L5_lock@2x.png\");insert into theme_lock (theme_id,level,unlock_url,lock_url) values (2,10,\"pooh_L10_unlock@2x.png\",\"pooh_L10_lock@2x.png\");insert into theme_lock (theme_id,level,unlock_url,lock_url) values (2,15,\"pooh_L15_unlock@2x.png\",\"pooh_L15_lock@2x.png\");";
    char * init_themeLevel_sql="insert into theme_level(theme_id,level,theme_url) values (1,0,\"theme1_lv0@2x.png\");insert into theme_level(theme_id,level,theme_url) values (1,1,\"theme1_lv1@2x.png\");insert into theme_level(theme_id,level,theme_url) values (1,2,\"theme1_lv2@2x.png\");insert into theme_level(theme_id,level,theme_url) values (1,3,\"theme1_lv3@2x.png\");insert into theme_level(theme_id,level,theme_url) values (1,4,\"theme1_lv4@2x.png\");insert into theme_level(theme_id,level,theme_url) values (1,5,\"theme1_lv5@2x.png\");insert into theme_level(theme_id,level,theme_url) values (1,6,\"theme1_lv6@2x.png\");insert into theme_level(theme_id,level,theme_url) values (1,7,\"theme1_lv7@2x.png\");insert into theme_level(theme_id,level,theme_url) values (1,8,\"theme1_lv8@2x.png\");insert into theme_level(theme_id,level,theme_url) values (1,9,\"theme1_lv9@2x.png\");insert into theme_level(theme_id,level,theme_url) values (1,10,\"theme1_lv10@2x.png\");insert into theme_level(theme_id,level,theme_url) values (1,11,\"theme1_lv11@2x.png\");insert into theme_level(theme_id,level,theme_url) values (1,12,\"theme1_lv12@2x.png\");insert into theme_level(theme_id,level,theme_url) values (1,13,\"theme1_lv13@2x.png\");insert into theme_level(theme_id,level,theme_url) values (1,14,\"theme1_lv14@2x.png\");insert into theme_level(theme_id,level,theme_url) values (1,15,\"theme1_lv15@2x.png\");insert into theme_level(theme_id,level,theme_url) values (2,0,\"theme2_lv0@2x.png\");insert into theme_level(theme_id,level,theme_url) values (2,1,\"theme2_lv1@2x.png\");insert into theme_level(theme_id,level,theme_url) values (2,2,\"theme2_lv2@2x.png\");insert into theme_level(theme_id,level,theme_url) values (2,3,\"theme2_lv3@2x.png\");insert into theme_level(theme_id,level,theme_url) values (2,4,\"theme2_lv4@2x.png\");insert into theme_level(theme_id,level,theme_url) values (2,5,\"theme2_lv5@2x.png\");insert into theme_level(theme_id,level,theme_url) values (2,6,\"theme2_lv6@2x.png\");insert into theme_level(theme_id,level,theme_url) values (2,7,\"theme2_lv7@2x.png\");insert into theme_level(theme_id,level,theme_url) values (2,8,\"theme2_lv8@2x.png\");insert into theme_level(theme_id,level,theme_url) values (2,9,\"theme2_lv9@2x.png\");insert into theme_level(theme_id,level,theme_url) values (2,10,\"theme2_lv10@2x.png\");insert into theme_level(theme_id,level,theme_url) values (2,11,\"theme2_lv11@2x.png\");insert into theme_level(theme_id,level,theme_url) values (2,12,\"theme2_lv12@2x.png\");insert into theme_level(theme_id,level,theme_url) values (2,13,\"theme2_lv13@2x.png\");insert into theme_level(theme_id,level,theme_url) values (2,14,\"theme2_lv14@2x.png\");insert into theme_level(theme_id,level,theme_url) values (2,15,\"theme2_lv15@2x.png\");";
    char *errorMsg;
    if(![db executeUpdate:create_user_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_user_sql,errorMsg);
        return NO;
    }
    if(![db executeUpdate:create_watch_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_watch_sql,errorMsg);
        return NO;
    }
    if(![db executeUpdate:create_manager_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_manager_sql,errorMsg);
        return NO;
    }
    /*
    if(sqlite3_exec(database, create_bind_sql, NULL, NULL, &errorMsg)!=SQLITE_OK)
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_bind_sql,errorMsg);
        return NO;
    }*/
    //if(sqlite3_exec(database,create_upgrade_sql, NULL, NULL, &errorMsg)!=SQLITE_OK)
    if(![db executeUpdate:create_upgrade_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_upgrade_sql,errorMsg);
        return NO;
    }
    if(![db executeUpdate:create_grow_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_grow_sql,errorMsg);
        return NO;
    }
    if(![db executeUpdate:create_systask_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_systask_sql,errorMsg);
        return NO;
    }
    if(![db executeUpdate:create_task_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_task_sql,errorMsg);
        return NO;
    }
    if(![db executeUpdate:create_theme_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_theme_sql,errorMsg);
        return NO;
    }
    if(![db executeUpdate:create_themeLock_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_themeLock_sql,errorMsg);
        return NO;
    }
    if(![db executeUpdate:create_themeLevel_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_themeLevel_sql,errorMsg);
        return NO;
    }
    if(![db executeUpdate:create_position_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_position_sql,errorMsg);
        return NO;
    }
    if(![db executeUpdate:create_fence_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_fence_sql,errorMsg);
        return NO;
    }
    if(![db executeUpdate:create_message_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_message_sql,errorMsg);
        return NO;
    }
    if(![db executeUpdate:create_event_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_event_sql,errorMsg);
        return NO;
    }
    if(![db executeUpdate:create_remind_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_remind_sql,errorMsg);
        return NO;
    }
    if(![db executeUpdate:create_chart_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,create_chart_sql,errorMsg);
        return NO;
    }
    if(![db executeStatements:init_themeData_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,init_themeData_sql,errorMsg);
        return NO;
    }
    if(![db executeStatements:init_themeLock_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,init_themeLock_sql,errorMsg);
        return NO;
    }
    if(![db executeStatements:init_themeLevel_sql])
    {
        NSLog(@"%@,Error: sql is %s,%s",tag,init_themeLevel_sql,errorMsg);
        return NO;
    }
    NSLog(@"%@,Create all tables successed.",tag);
    return YES;
}

//批量插入数据
-(BOOL) execSql:(NSArray *)sqlArray {
    NSLog(@"%@,execSql entered.",tag);
    if(sqlArray==NULL||[sqlArray count]<=0)
    {
        NSLog(@"%@,execSql,sqlArray is null",tag);
        return NO;
    }
    __block BOOL result=YES;
    [queue inDatabase:^(FMDatabase *db2) {
        if(db2==NULL||![db2 open])
        {
            NSLog(@"%@,execSql,open db2 error.",tag);
            result=NO;
            return ;
        }
        for(int i=0;i<[sqlArray count];i++)
        {
            NSString * sql=(NSString *)[sqlArray objectAtIndex:i];
            if(sql==NULL)
                continue;
            BOOL res=[db2 executeStatements:[sql UTF8String]];
            if(res)
            {
                NSLog(@"%@,execSql,exec sucess,sql is %@",tag,sql);
            }
            else
            {
               NSLog(@"%@,execSql,Error: sql is %@",tag,sql);
            }

        }
     //关闭数据库
     [db2 close];
    }];
    return result;
}

//插入数据
-(BOOL) insertData:(const char *)sql  tableName:(NSString *) tableName{
    NSLog(@"%@,insertData entered.sql is %s",tag,sql);
    if(sql==NULL||strlen(sql)<=0)
    {
        NSLog(@"%@,sql is null",tag);
        return NO;
    }
    __block BOOL result=YES;
    [queue inDatabase:^(FMDatabase *db2) {
        if(db2==NULL||![db2 open])
        {
            NSLog(@"%@,insertData,open db2 error.",tag);
            result=NO;
            return ;
        }
        result=[db2 executeUpdate:sql];
        [db2 close];
     }];
        return result;
}


//插入数据
-(long) insertDataRet:(const char *)sql  tableName:(NSString *) tableName{
    NSLog(@"%@,insertData entered.sql is %s",tag,sql);
    if(sql==NULL||strlen(sql)<=0)
    {
        NSLog(@"%@,sql is null",tag);
        return 0;
    }
    __block long insertId=0;
    [queue inDatabase:^(FMDatabase *db2) {
        if(db2==NULL||![db2 open])
        {
            NSLog(@"%@,insertDataRet,open db2 error.",tag);
            insertId=0;
            return ;
        }
        [db2 executeUpdate:sql];
        insertId=(long)[db2 lastInsertRowId];
        [db2 close];
    }];
    return insertId;
}


//更新数据
-(BOOL) updateData:(const char *)sql tableName:(NSString *)tableName{
    NSLog(@"%@,updateData entered.sql is %s",tag,sql);
    if(sql==NULL||strlen(sql)<=0)
    {
        NSLog(@"%@,sql is null",tag);
        return NO;
    }
    __block BOOL result=YES;
    [queue inDatabase:^(FMDatabase *db2) {
        if(db2==NULL||![db2 open])
        {
            NSLog(@"%@,updateData,open db2 error.",tag);
            result=NO;
            return ;
        }
        result=[db2 executeUpdate:sql];
        [db2 close];
    }];
    return result;
}
//删除数据
- (BOOL) deleteData:(const char *)sql tableName:(NSString *)tableName{
    NSLog(@"%@,deleteData entered.sql is %s",tag,sql);
    if(sql==NULL||strlen(sql)<=0)
    {
        NSLog(@"%@,sql is null",tag);
        return NO;
    }
    __block BOOL result=YES;
    [queue inDatabase:^(FMDatabase *db2) {
        if(db2==NULL||![db2 open])
        {
            NSLog(@"%@,deleteData,open db2 error.",tag);
            result=NO;
            return ;
        }
        result=[db2 executeUpdate:sql];
        [db2 close];
    }];
    return result;
    
}
//查询数据
- (NSMutableArray*)searchData:(const char *)sql{
    if(sql==NULL||strlen(sql)<=0)
    {
        NSLog(@"%@,sql is null",tag);
        return NULL;
    }
    NSMutableArray *array = [[NSMutableArray alloc] init];
    //判断数据库是否打开
    if ([self openDB]) {
        @try {
              [queue inDatabase:^(FMDatabase *db2) {
                if(db2==NULL||![db2 open])
                {
                    NSLog(@"%@,searchData,open db2 error.",tag);
                    return ;
                }
                FMResultSet *rs=[db2 executeQuery:[NSString stringWithUTF8String:sql]];
                if(rs==NULL)
                {
                    NSLog(@"%@,searchData,exec sql is not ok.,sql is %s.",tag,sql);
                    [db2 close];
                    return ;
                }
                NSDictionary *columns=[rs columnNameToIndexMap];
                if(columns==NULL||[columns count]==0)
                {
                    NSLog(@"%@,searchData,sql is %s,columns is null.",tag,sql);
                    [rs close];
                    [db2 close];
                    return ;
                }
                NSArray *columnArray=[columns allKeys];
                if(columnArray==NULL||[columnArray count]==0)
                {
                    NSLog(@"%@,searchData,sql is %s,columnArray is null.",tag,sql);
                    [rs close];
                    [db2 close];
                    return ;
                }
                while ([rs next]) {
                    NSMutableDictionary *dict=[[NSMutableDictionary alloc] init];
                    for(int i=0;i<[columnArray count];i++)
                    {
                        NSString*    key = [columnArray objectAtIndex:i];
                        if(key==NULL)
                            continue;
                        NSString*    value = [rs stringForColumn:key];
                        
                        if(value==NULL)
                        {
                            [dict setValue:NULL forKey:key];
                        }
                        else
                        {
                            [dict setObject:value forKey:key];
                        }
                        value=NULL;
                        key=NULL;
                        
                    }
                    if(dict!=NULL)
                    {
                        [array addObject:dict];
                        dict=NULL;
                    }
                    
                }
                [rs close];
                [db2 close];
                NSLog(@"%@,searchData,result: sql is %s,row is %d ",tag,sql,(int)[array count]);

            }];
            return array;
        }
  
       @catch (NSException *ex) {
            NSLog(@"%@,searchData,sql is %s,exception msg is %@ ",tag,sql,ex.reason);
        }
        @finally {

        }
    }
    
    return NULL ;
}
@end

